package game.level.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


import game.level.dao.IALevelDao;
import game.level.pojo.ALevel;
import game.util.DBUtil;

public class ALevelDaoImpl implements IALevelDao
{
	/*添加一条信息*/
	public int insertDao(ALevel level)
	{
		String sql = "INSERT INTO `game`.`a_level`(`name`, `stScore`, `edScore`, `content`, `status`, `createTime`, `updateTime`, `pubTime`) VALUES"
				+ " (?,?,?,?,?,?,?,?)";
		DBUtil db = new DBUtil();
		/*获取连接*/
		Connection con = db.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		try
		{
			/*连接Sql句*/
			ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			/*为占位符赋值*/
			ps.setObject(1, level.getName());
			ps.setObject(2, level.getStScore());
			ps.setObject(3, level.getEdScore());
			ps.setObject(4, level.getContent());
			ps.setObject(5, level.getStatus());
			ps.setObject(6, level.getCreateTime());
			ps.setObject(7, level.getUpdateTime());
			ps.setObject(8, level.getPubTime());
			
			int res = ps.executeUpdate() ;
			/*获取下一个主键*/
			rs = ps.getGeneratedKeys();
			if(rs.next())
			{
				int id = rs.getInt(1);
				level.setId(id);
			}
			return res ;
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			db.closeAll(con, ps, rs);
		}
		return 0;
	}
	/*更新一条信息*/
	public int updateDao(ALevel level)
	{
		String sql = "update a_level set name = ? ,stScore=?,edScore = ?, content = ? , status = ? , "
				+ "createTime = ? , updateTime = ? , pubTime = ? "
				+ "where id = ? " ;
		
		DBUtil dbUtil = new DBUtil() ; 
		/* 获取一个链接 */
		Connection connection = dbUtil.getConnection() ; 
		PreparedStatement pst = null ;
		ResultSet rs = null ; 
		try
		{
			/* 创建sql语句 
			 * 参数2:是为了返回主键
			 * */
			pst = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			/* 为?(占位符)赋值 */
			pst.setObject(1, level.getName());
			pst.setObject(2, level.getStScore());
			pst.setObject(3, level.getEdScore());
			pst.setObject(4, level.getContent());
			pst.setObject(5, level.getStatus());
			pst.setObject(6, level.getCreateTime());
			pst.setObject(7, level.getUpdateTime());
			pst.setObject(8, level.getPubTime());
			pst.setObject(9, level.getId());
			int res = pst.executeUpdate() ;
			/* 获取一下数据库中的主键 */
			rs = pst.getGeneratedKeys() ;
			/* 主键可是一行一列 
			 * getInt只有在不知道列名的时候,就使用第几列
			 * */
			if(rs.next())
			{
				int id = rs.getInt(1);
				level.setId(id);
			}
			return res ; 
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			dbUtil.closeAll(connection, pst, rs);
		}
		return 0;
	}
	/*删除一条信息*/
	public int deleteDao(ALevel level)
	{
		return 0;
	}
	/*查询一条信息*/
	public ALevel findOneDao(Map<String, Object> condMap)
	{
		List<Object> paramsList = new ArrayList<Object>();
		String sql = "select * from a_level " ; 
		if(condMap.get("currScore") != null)
		{
			 sql = sql + " where stScore < ? and edScore > ? " ;
			 //paramsList.add(condMap.get("currScore"));
			 paramsList.add(condMap.get("currScore"));
		}else
		{
			sql = sql + "where id = ?" ; 
			paramsList.add(condMap.get("id")) ; 
		}
		/*当前分数*/
		DBUtil db = new DBUtil();
		/*获取一个连接*/
		Connection con = db.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		try
		{
			/*第二个参数返回主键*/
			ps = con.prepareStatement(sql , Statement.RETURN_GENERATED_KEYS);
			/*为占位符赋值*/
			int count = 1;
			for (Iterator iterator = paramsList.iterator(); iterator.hasNext();)
			{
				Object val = (Object) iterator.next();
				ps.setObject(count, val);
				count++;
			}
			/*获取下一个主键*/
			rs = ps.executeQuery();
			if(rs.next())
			{
				ALevel level = converFromDb(rs);
				return level ;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			db.closeAll(con, ps, rs);
		}
		return null;
	}
	/*查询全部信息*/
	public List<ALevel> findCondListDao(Map<String, Object> condMap)
	{
		/*存储所有的参数*/
		List<Object> paramList = new ArrayList<Object>();
		/*存储所有的结果*/
		List<ALevel> resultList = new ArrayList<ALevel>();
		/*创建Sql语句*/
		String sql = "select * from a_level" ; 
		
		/* 加上状态 */
		if(condMap.get("status") != null 
			&& !"".equalsIgnoreCase(condMap.get("status") + " "))
		{
			sql = sql + " where status = ? " ;
			paramList.add(condMap.get("status"));
		}
		
		sql = sql + " order by pubTime asc ";
		DBUtil db =new DBUtil();
		/*获取一个连接*/
		Connection con = db.getConnection() ;
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		try
		{
			ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			int count = 1 ;
			for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
			{
				Object val = (Object) iterator.next();
				ps.setObject(count, val);
				count++;
			}
			/*获取下一个数据库的主键*/
			rs = ps.executeQuery();
			while(rs.next())
			{
				ALevel level = converFromDb(rs);
				/*一定要将level增加到List中*/
				resultList.add(level);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			db.closeAll(con, ps, rs);
		}
		return resultList;
	}
	private ALevel converFromDb(ResultSet rs) throws SQLException
	{
		/* 将数据库中的一条记录,与java中的对象一一对应起来 */
		int id = rs.getInt("id");
		String name = rs.getString("name");
		int stScore = rs.getInt("stScore");
		int edScore = rs.getInt("edScore");
		String content = rs.getString("content");
		byte status = rs.getByte("status");
		Date createTime = rs.getTimestamp("createTime");
		Date updateTime = rs.getTimestamp("updateTime");
		Date pubTime = rs.getTimestamp("pubTime");
		
		ALevel level = new ALevel() ; 
		level.setId(id);
		level.setName(name);
		level.setStScore(stScore);
		level.setEdScore(edScore);
		level.setContent(content);
		level.setStatus(status);
		level.setCreateTime(createTime);
		level.setUpdateTime(updateTime);
		level.setPubTime(pubTime);
		return level;
	}
}
